unit main;

{$mode objfpc}{$H+}

{
   creating a Thunderbird message:
   http://kb.mozillazine.org/Command_line_arguments_%28Thunderbird%29
}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, Menus,
  ComCtrls, ExtCtrls, Grids, StdCtrls, Sqlite3DS, sqlite3conn, IniFiles,
  clientdb, lNet, db, fpstructs, tcpbuffers, process;

type

  TShiftData = record
    TerminalID: Integer;
    starttime: TDateTime;
    endtime: TDateTime;
    checksum: string;
  end;

  { TMainForm }

  TMainForm = class(TForm)
    ApplyServerChangesButton: TButton;
    MenuItem10: TMenuItem;
    miEmailSupplier: TMenuItem;
    miSupplierOrder: TMenuItem;
    miEditSupplier: TMenuItem;
    miNewSupplier: TMenuItem;
    ResetServerDetailsButton: TButton;
    DisplayFilterButton: TButton;
    DisplayLabel: TLabel;
    ServerAddressEdit: TEdit;
    ServerPortEdit: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    miReceiveOrder: TMenuItem;
    MenuItem8: TMenuItem;
    StatusBar: TStatusBar;
    SuppliersPopupMenu: TPopupMenu;
    ShiftReportMenuItem: TMenuItem;
    ShiftsPopup: TPopupMenu;
    ShiftGrid: TStringGrid;
    EventsGrid: TStringGrid;
    SupplierGrid: TStringGrid;
    SupplierTab: TTabSheet;
    TerminalListBox: TListBox;
    MenuItem7: TMenuItem;
    miPrintLabels: TMenuItem;
    MenuItem9: TMenuItem;
    miAllProdSales: TMenuItem;
    miAllServSales: TMenuItem;
    miApplyUpdates1: TMenuItem;
    miDeleteProduct1: TMenuItem;
    miEditService: TMenuItem;
    miItemSales1: TMenuItem;
    miNewService: TMenuItem;
    ServicesPopupMenu: TPopupMenu;
    ServicesListGrid: TStringGrid;
    ProductSearchEdit: TEdit;
    ExportButton: TButton;
    FilterBox: TEdit;
    FilterPanel: TPanel;
    ProductSearchLabel: TLabel;
    MainMenu1: TMainMenu;
    MenuItem1: TMenuItem;
    MenuItem2: TMenuItem;
    MenuItem3: TMenuItem;
    MenuItem4: TMenuItem;
    FileNewMenuItem: TMenuItem;
    MenuItem5: TMenuItem;
    MenuItem6: TMenuItem;
    miApplyUpdates: TMenuItem;
    miNewProduct: TMenuItem;
    miDeleteProduct: TMenuItem;
    miEditProduct: TMenuItem;
    miItemSales: TMenuItem;
    BasePageControl: TPageControl;
    ProductListGrid: TStringGrid;
    ProductPopupMenu: TPopupMenu;
    ReportPageControl: TPageControl;
    ReportsTab: TTabSheet;
    SaveDialog: TSaveDialog;
    StockTab: TTabSheet;
    ServerTab: TTabSheet;
    ServicesTab: TTabSheet;
    TerminalTab: TTabSheet;
    procedure ApplyServerChangesButtonClick(Sender: TObject);
    procedure BasePageControlPageChanged ( Sender: TObject ) ;
    procedure DisplayFilterButtonClick(Sender: TObject);
    procedure ExportButtonClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure MenuItem4Click(Sender: TObject);
    procedure MenuItem5Click(Sender: TObject);
    procedure MenuItem8Click(Sender: TObject);
    procedure miAllProdSalesClick(Sender: TObject);
    procedure miApplyUpdatesClick(Sender: TObject);
    procedure miEditProductClick(Sender: TObject);
    procedure miEditSupplierClick(Sender: TObject);
    procedure miItemSalesClick(Sender: TObject);
    procedure miNewProductClick(Sender: TObject);
    procedure miNewSupplierClick(Sender: TObject);
    procedure miPrintLabelsClick(Sender: TObject);
    procedure miReceiveOrderClick(Sender: TObject);
    procedure miSupplierOrderClick(Sender: TObject);
    procedure ProductListGridEditingDone ( Sender: TObject ) ;
    procedure ProductListGridGetEditText ( Sender: TObject; ACol,
      ARow: Integer; var Value: string ) ;
    procedure ProductListGridPrepareCanvas ( sender: TObject; aCol,
      aRow: Integer; aState: TGridDrawState ) ;
    procedure ProductListGridSelectCell ( Sender: TObject; aCol, aRow: Integer;
      var CanSelect: Boolean ) ;
    procedure ProductSearchEditChange(Sender: TObject);
    procedure ProductSearchEditEnter(Sender: TObject);
    procedure ProductSearchEditExit(Sender: TObject);
    procedure ResetServerDetailsButtonClick(Sender: TObject);
    procedure ServerAddressEditChange(Sender: TObject);
    procedure ShiftReportMenuItemClick ( Sender: TObject ) ;
    procedure SupplierGridSelectCell ( Sender: TObject; aCol, aRow: Integer;
      var CanSelect: Boolean ) ;
    procedure TerminalListBoxSelectionChange ( Sender: TObject; User: boolean
      ) ;
  private
    { private declarations }
    Terminals: TClientDB;
    fLoadingData: Boolean;
    fShifts: array of TShiftData;
    fWaiting: Boolean;
    fCellValue: string;
    function SendTerminalID(aSocket: TLSocket): integer;
    procedure ReceiveFile(db: TObject; tcpBuf: TTCPBuffer);
  public
    { public declarations }
    SupplierList: array of TfpSupplierInfo;
    ProductListQuery: string;
    procedure RefreshProductList;
    procedure RefreshServicesList;
    procedure GetTerminals;
    procedure GetShifts( terminalindex: integer);
    procedure GetSupplierList;
    function NewSupplier( var SupplierName: string ): Boolean;
    procedure GetSalesReport(  terminalID: integer; startTime, endTime: TDateTime);
    procedure GetEvents( terminalID: integer; startTime, endTime: TDateTime);
  end;


var
  MainForm: TMainForm;
  MainDB: TClientDB;
  fpserver: string;
  fpport: Integer;

const
  TERMINALSPAGE= 1;
  STOCKPAGE    = 2;
  SERVICESPAGE = 3;
  SUPPLIERPAGE = 4;
  REPORTPAGE   = 5;

implementation

uses displayfilter, product, salesfilter, labeller, fposUtils, orderin, interfacedesign, fromtodlg, supplier,
  supplierstock;

{$R *.lfm}

{ TMainForm }

procedure TMainForm.MenuItem4Click(Sender: TObject);
begin
  Close;
end;

procedure TMainForm.DisplayFilterButtonClick(Sender: TObject);
begin
  if ProductFilterForm.ShowModal=mrOK then
     begin
       ProductListQuery := 'SELECT * FROM (select products.productid, products.barcode AS barcode, products.name AS name, suppliers.name AS supplier, costprice, saleprice, stocklevel, discontinued '
           + 'from products, suppliers where suppliers.id=products.suppliercode' + ProductFilterForm.FilterSQL + ') products'
           + ' LEFT JOIN (select distinct itemid, SUM(quantity) AS sold from saledetails GROUP BY Itemid) sold ON sold.itemID=products.productid'
           + ' ORDER BY barcode';
       RefreshProductList;
       FilterBox.Text := ProductFilterForm.FilterText;
     end;
end;

procedure TMainForm.BasePageControlPageChanged ( Sender: TObject ) ;
begin
  case BasePageControl.PageIndex of
       STOCKPAGE    : if ProductListGrid.RowCount<10 then RefreshProductList;
       SERVICESPAGE : if ServicesListGrid.RowCount<2 then RefreshServicesList;
       TERMINALSPAGE : if TerminalListBox.Count<1 then GetTerminals;
       SUPPLIERPAGE: if SupplierGrid.RowCount<2 then GetSupplierList;
  end;
end;

procedure TMainForm.ApplyServerChangesButtonClick(Sender: TObject);
begin
  fpserver := ServerAddressEdit.Text;
  if not TryStrToInt(ServerPortEdit.Text, fpport)
     then ServerPortEdit.Text := IntToStr(fpport);

  Settings.WriteString('Server', 'ServerAddress', fpserver);
  Settings.WriteInteger('Server', 'ServerPort', fpport);
{  ProductListGrid.RowCount := 0;
  ServicesListGrid.RowCount := 0;
  TerminalListBox.Clear;
  ShiftGrid.RowCount := 0;
  SupplierGrid.RowCount := 0;
  MainDB.Disconnect;
  MainDB.Server := fpserver;
  MainDB.Port := fpport;
  MainDB.Connect;
  Terminals.Disconnect;
  Terminals.Server := fpserver;
  Terminals.Port := fpport;
  Terminals.Connect;
  Terminals.SQL := 'Select * FROM Terminals ORDER BY name';
  Terminals.Open; }
end;

procedure TMainForm.ExportButtonClick(Sender: TObject);
var
  Tabdata: TStringList;
  x: Integer;
  aline: string;
  y: Integer;
begin
  if SaveDialog.Execute then
     begin
       Tabdata := TStringList.Create;
       aline := ProductListGrid.Columns[0].Title.Caption;
       for x := 1 to ProductListGrid.ColCount-1 do
           aline := aline + #9 + ProductListGrid.Columns[x].Title.Caption;
       Delete(aline, Length(aline), 1);
       Tabdata.Add(aline);
       for x := 1 to ProductListGrid.RowCount-1 do
           begin
             aline := ProductListGrid.Cells[0,x];
             for y := 1 to ProductListGrid.ColCount-1 do
                 aline := aline + #9 + ProductListGrid.Cells[y, x];
             Delete(aline, Length(aline), 1);
             Tabdata.Add(aline);
           end;
       Tabdata.SaveToFile(SaveDialog.FileName);
       Tabdata.Free;
     end;
end;

procedure TMainForm.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  MainDB.Close;
  MainDB.Free;
end;

procedure TMainForm.FormCreate(Sender: TObject);
begin
  Settings := TIniFile.Create(GetFirePOSConfigDir + 'fireman.ini');
  {$IFDEF DEBUG} writeln('Using config file: ', Settings.FileName); {$ENDIF}
  fpserver := Settings.ReadString('Server', 'ServerAddress', 'localhost');
  ServerAddressEdit.Text := fpserver;
  fpport := Settings.ReadInteger('Server', 'ServerPort', DEFAULT_PORT);
  ServerPortEdit.Text := IntToStr(fpport);
  ApplyServerChangesButton.Enabled := false;
  ResetServerDetailsButton.Enabled := false;

  StatusBar.Panels[2].Text := 'Using FirePOS server at ' + fpserver;
  MainDB := TClientDB.Create;
  MainDB.Server := fpserver;
  MainDB.Port := fpport;
  MainDB.OnReceiveFile := @ReceiveFile;
      ProductListQuery := 'SELECT * FROM (SELECT products.productid, products.barcode AS barcode, products.name AS name, suppliers.name AS supplier, costprice, saleprice, stocklevel, discontinued '
           + 'FROM products, suppliers WHERE suppliers.id=products.suppliercode) products '
           + ' LEFT JOIN (select distinct itemid, SUM(quantity) AS sold from saledetails GROUP BY Itemid) sold ON sold.itemID=products.productid'
           + ' ORDER BY barcode';
  Terminals := TClientDB.Create;
  Terminals.server := fpserver;
  Terminals.Port := fpport;
  Terminals.SQL := 'Select * FROM Terminals ORDER BY name';
  Terminals.Open;

  CurrencyString := Settings.ReadString('Preferences', 'CurrencySymbol', '£');
end;

procedure TMainForm.MenuItem5Click(Sender: TObject);
var
  ReportPage: TTabSheet;
begin
  SalesFilterForm := TSalesFilterForm.Create(nil);
  Terminals.First;
  While not Terminals.EOF do
        begin
          SalesFilterForm.TerminalSelect.Items.AddObject(Terminals.FieldByName('name').AsString,
                                          TObject( Terminals.FieldByName('TerminalID' ).AsInteger));
          Terminals.Next;
        end;
  if SalesFilterForm.ShowModal=mrOK then
    begin
      ReportPage := TTabSheet.Create(nil);
      ReportPage.Caption := 'New Report';
      ReportPage.Color := clWhite;
      ReportPage.PageControl := ReportPageControl;
      BasePageControl.PageIndex := 0;
    end;
end;

procedure TMainForm.MenuItem8Click(Sender: TObject);
var
  IFDesigner: TInterfaceDesignForm;
begin
  IFDesigner := TInterfaceDesignForm.Create(nil);
  IFDesigner.RefreshPages;
  IFDesigner.GetButtons(0);
  IFDesigner.ShowModal;
  IFDesigner.Free;
end;

procedure TMainForm.miAllProdSalesClick(Sender: TObject);
var
  r: integer;
  query: String;
  ReportPage: TTabSheet;
  ReportGrid: TStringGrid;
  ProductServiceFlag: String;
begin
  if Sender = miAllProdSales
     then ProductServiceFlag := 'NOT'
     else ProductServiceFlag := '';
  FromToForm.MainLabel.Caption := 'Show sales between the following dates:';
  FromToForm.ToDateEdit.Date := Now;
  FromToForm.FromDateEdit.Date := StrToDate('01' + DateSeparator + '04' + DateSeparator + '2012');
  if FromToForm.ShowModal<>mrOK then Exit;
  try
   { query := 'select Products.name AS product, Departments.name AS dept, SUM(sales.SaleDetails.quantity) AS totalsales '
             + 'FROM Products, sales.SaleDetails, Departments '
             + 'WHERE Products.productid=sales.SaleDetails.itemid AND Products.barcode IS ' + ProductServiceFlag + ' NULL AND Departments.id=Products.dept '
             + 'GROUP BY Products.productid ORDER BY products.dept, products.name;';   }
    query := 'select Products.name AS product, Departments.name AS dept, SUM(itemquantity) AS totalsales, stocklevel '
             + 'FROM Products, Departments, '
                + '(SELECT sales.saledetails.itemid as saleitemid, sales.SaleDetails.quantity AS itemquantity '
                + 'from sales.SaleDetails, sales.Salesummary '
                + 'WHERE sales.Salesummary.saletime>' + DateTimeToJulianDateTimeAsString(FromToForm.FromDateEdit.Date)
                + ' AND sales.Salesummary.saletime<' + IntToStr(Trunc(FromToForm.ToDateEdit.Date+1))
                + ' AND sales.saledetails.saleid=sales.salesummary.saleid ) '
             + 'WHERE Products.productid=saleitemid AND Products.barcode IS NOT NULL AND Departments.id=Products.dept '
             + 'GROUP BY Products.productid ORDER BY products.dept, products.name;';
//writeln(query);
    MainDB.SQL := query;
    MainDB.Open;
    if MainDB.RecordCount>0 then
    begin
      ReportPage := TTabSheet.Create(Self);
      if Sender = miAllProdSales
         then ReportPage.Caption := 'Product Sales'
         else ReportPage.Caption := 'Services Sales';
      ReportPage.Color := clWhite;
      ReportPage.PageControl := ReportPageControl;
      BasePageControl.PageIndex := 0;
      ReportGrid := TStringGrid.Create(Self);
      with ReportGrid do
           begin
             Parent := ReportPage;
             Align := alClient;
             ColCount := 4;
             FixedCols := 0;
             RowCount := MainDB.RecordCount+1;
             ColWidths[0] := 300;
             ColWidths[1] := 120;
             Cells[0, 0] := 'Item';
             Cells[1, 0] := 'Dept';
             Cells[2, 0] := 'Sales';
             Cells[3, 0] := 'Stock';
             r := 1;
             While Not MainDB.EOF do
                   begin
                     Cells[0, r] := MainDB.FieldByName('product').AsString;
                     Cells[1, r] := MainDB.FieldByName('dept').AsString;
                     Cells[2, r] := MainDB.FieldByName('totalsales').AsString;
                     Cells[3, r] := MainDB.FieldByName('stocklevel').AsString;
                     Inc(r);
                     MainDB.Next;
                   end;
           end;
    end;
  finally
    MainDB.Close;
  end;
end;

procedure TMainForm.miApplyUpdatesClick(Sender: TObject);
var
  sendbuf: TTCPSendBuffer;
begin
  sendbuf := TTCPSendBuffer.Create(nil, nil);
  sendbuf.Header := NewHeader(ADMIN_ID, fpmSubmitConfigData);
  MainDB.Connection.SendMessage(sendbuf);
//  miApplyUpdates.Enabled := not LTCPClient.Send(header, SizeOf(header))=SizeOf(header);
end;

procedure TMainForm.miEditProductClick(Sender: TObject);
 var
  PF: TProductForm;
  r: LongInt;
begin
  PF := TProductForm.Create(nil);
  r := ProductListGrid.Row;
  if PF.ShowEdit(ProductListGrid.Cells[0, r])=mrOK then
     begin
       RefreshProductList;
       ProductListGrid.Row := r;
       miApplyUpdates.Enabled := true;
     end;
  PF.Free;
end;

procedure TMainForm.miEditSupplierClick(Sender: TObject);
var
  supplierdata: TfpSupplierInfo;
begin
  supplierdata := SupplierList[SupplierGrid.Row-1];
  SupplierForm := TSupplierForm.Create(Self);
  with SupplierForm do
       begin
         SupplierForm.Caption := 'Edit Supplier';
         supplierid := supplierdata.id;
         SupplierName.Text := supplierdata.name;
         AccountNum.Text := supplierdata.accountnum;
         AddressMemo.Text := supplierdata.address;
         PostCode.Text := supplierdata.postcode;
         Telephone.Text := supplierdata.telephone;
         Email.Text:= supplierdata.email;
         ContactName.Text := supplierdata.contact;
         NotesMemo.Text := supplierdata.notes;
         WebsiteAddress.Text := supplierdata.website;
       end;
  try
  if SupplierForm.ShowModal = mrOk then
    begin
      if MainDB.ExecRemoteSQL( 'UPDATE suppliers '
                 + ' SET name="' + EscapeQuotes( SupplierForm.SupplierName.Text ) + '",'
                 + ' accountnum="' + SupplierForm.AccountNum.Text + '",'
                 + ' address="' + EscapeQuotes(EscapeLineEnding( SupplierForm.AddressMemo.Text ) ) + '",'
                 + ' postcode="' + SupplierForm.PostCode.Text + '",'
                 + ' telephone="' + SupplierForm.Telephone.Text + '",'
                 + ' email="' + SupplierForm.Email.Text + '"'
                 + ' WHERE id=' + IntToStr(supplierdata.id) + ';')
         then
           begin
             GetSupplierList;
           end
         else
           begin
             ShowMessage('Error when editing supplier');
           end;
    end;
  finally
  SupplierForm.Free;
  end;
end;

procedure TMainForm.miItemSalesClick(Sender: TObject);
var
  query: String;
  bc: String;
begin
  bc := ProductListGrid.Cells[0, ProductListGrid.Row];
  if bc[1]='2' then bc := Copy( bc, 1, 7 );
  ShowMessage(IntToStr(Integer(ProductListGrid.Objects[0,ProductListGrid.Row])));
  {MainDB.Close;
  try
    query := 'select SUM(quantity) AS totalsales FROM sales.SaleDetails WHERE sales.SaleDetails.itemID='
             + '(SELECT productid FROM Products WHERE barcode="' + bc + '");';
{$IFDEF DEBUG}
writeln(query);
{$ENDIF}
    MainDB.SQL := query;
    MainDB.Open;
    if MainDB.RecordCount>0 then
       ShowMessage(' Total sales of ' + ProductListGrid.Cells[1, ProductListGrid.Row] + ': ' + MainDB.FieldByName('totalsales').AsString);
  finally
    MainDB.Close;
  end; }
end;

procedure TMainForm.miNewProductClick(Sender: TObject);
 var
  PF: TProductForm;
  r: LongInt;
begin
  PF := TProductForm.Create(nil);
  r := ProductListGrid.Row;
  if PF.ShowNew = mrOK then
    begin
      RefreshProductList;
      miApplyUpdates.Enabled := True;
    end;
  PF.Free;
end;

procedure TMainForm.miNewSupplierClick(Sender: TObject);
var
  sn: string;
begin
  NewSupplier(sn);
end;

procedure TMainForm.miPrintLabelsClick(Sender: TObject);
var
  newlabel: TLabelInfo;
begin
  PrintLabelDialog.BarcodeEdit.Text := ProductListGrid.Cells[0, ProductListGrid.Row];
  PrintLabelDialog.PriceEdit.Text := CurrencyString + ProductListGrid.Cells[4, ProductListGrid.Row];
  PrintLabelDialog.TextCombo.Items[1] := ProductListGrid.Cells[1, ProductListGrid.Row];
  PrintLabelDialog.LabelTypeCombo.Enabled := not LabelPrinter.Active;
  PrintLabelDialog.FirstLabelEdit.Value := LabelPrinter.FirstLabelIndex;
  PrintLabelDialog.FirstLabelEdit.Enabled := not LabelPrinter.Active;
  if PrintLabelDialog.ShowModal=mrOK then
     begin
       if not LabelPrinter.Active then
          begin
            case PrintLabelDialog.LabelTypeCombo.ItemIndex of
                 0: LabelPrinter.Labeltype := ltPrice;
                 1: LabelPrinter.Labeltype := ltBarcodePrice;
                 else LabelPrinter.Labeltype := ltNone;
            end;
            LabelPrinter.FirstLabelIndex := PrintLabelDialog.FirstLabelEdit.Value;
          end;
       LabelPrinter.PrinterIndex := PrintLabelDialog.PrinterCombo.ItemIndex;
       newlabel.labeltype := ltBarcodePrice;
       newlabel.price := PrintLabelDialog.PriceEdit.Text;
       newlabel.barcode := PrintLabelDialog.BarcodeEdit.Text;
       newlabel.text := PrintLabelDialog.TextCombo.Text;
       newlabel.count := PrintLabelDialog.LabelcountEdit.Value;
       LabelPrinter.PrintLabels(newlabel, PrintLabelDialog.FullSheetCheck.Checked);
     end;
end;

procedure TMainForm.miReceiveOrderClick(Sender: TObject);
var
  OFM: TOrderInForm;
 begin
   OFM := TOrderInForm.Create(self);
   OFM.Caption := SupplierGrid.Cells[0, SupplierGrid.Row]
                  + ' - Order received';
   OFM.SupplierID := IntToStr( SupplierList[SupplierGrid.Row-1].id );
   OFM.Suppliername :=  SupplierGrid.Cells[0, SupplierGrid.Row] ;
   OFM.ShowModal;
end;

procedure TMainForm.miSupplierOrderClick(Sender: TObject);
var
  query: String;
  r: Integer;
begin
  query := 'SELECT productid, Products.name AS product, solditems.itemsales, costprice, saleprice, stocklevel, suppliersref FROM Products '
            + 'LEFT JOIN (SELECT saledetails.itemid as saleitemid, SUM(SaleDetails.quantity) AS itemsales from SaleDetails GROUP BY saleitemid) solditems '
            + 'ON Products.productid=solditems.saleitemid WHERE suppliercode=' + IntToStr(SupplierList[SupplierGrid.Row-1].id)
            + ' AND products.discontinued=0 GROUP BY Products.productid ORDER BY products.name;';
  MainDB.SQL := query;
  writeln(query);
  MainDB.Open;
  if MainDB.State=dsBrowse then
     begin
       SupplierStockForm := TSupplierStockForm.Create(nil);
       with SupplierStockForm do
            begin
              Caption := 'Order stock';
              SupplierIndex := SupplierGrid.Row-1;
              StockGrid.RowCount := MainDB.RecordCount+1;
              SupplierNameLabel.Caption := 'Supplier: ' + SupplierList[SupplierGrid.Row-1].name;
              Label1.Caption := '';
              r := 1;
              while not MainDB.EOF do
               begin
                 StockGrid.Objects[0, r] := TObject(MainDB.FieldByName('productid').AsInteger);
                 StockGrid.Cells[0, r] := MainDB.FieldByName('suppliersref').AsString;
                 StockGrid.Cells[1, r] := MainDB.FieldByName('product').AsString;
                 StockGrid.Cells[2, r] := MainDB.FieldByName('itemsales').AsString;
                 StockGrid.Cells[3, r] := MainDB.FieldByName('stocklevel').AsString;
                 StockGrid.Cells[4, r] := FormatFloat('0.00  ', MainDB.FieldByName('costprice').AsFloat);
                 StockGrid.Cells[5, r] := FormatFloat('0.00  ', MainDB.FieldByName('saleprice').AsFloat);
                 MainDB.Next;
                 Inc(r);
               end;
               MainDB.Close;
               MainDB.SQL := 'SELECT InvoiceAddress, DeliveryAddress, ContactName FROM BusinessDetails;';
               MainDB.Open;
               InvoiceMemo.Text := UnEscapeLineEnding(MainDB.FieldByName('InvoiceAddress').AsString) ;
               DeliveryMemo.Text := UnEscapeLineEnding(MainDB.FieldByName('DeliveryAddress').AsString);
               ContactEdit.Text := MainDB.FieldByName('ContactName').AsString;
               MainDB.Close;
               ShowModal;
               Free;
            end;
     end;
end;

procedure TMainForm.ProductListGridEditingDone ( Sender: TObject ) ;
var
  r: Integer;
  c: Integer;
  currencyval: Double;
  stock: integer;
  id: Integer;
  SQL: String;
  isupdate: Boolean;
begin
  r := ProductListGrid.Row;
  c := ProductListGrid.Col;
  if ProductListGrid.Cells[c,r]=fCellValue then Exit; //no changes made
  id := Integer(ProductListGrid.Objects[0,r]);
    case c of
       3, 4: if TryStrToFloat(ProductListGrid.Cells[c,r], currencyval) then
             begin
               SQL := 'UPDATE Products SET costprice=' + ProductListGrid.Cells[3,r]
                      + ', saleprice=' + ProductListGrid.Cells[4,r]
                      + ' WHERE productid=' + IntToStr(id) + ';';
               isupdate := true;
             end;
       5:    if TryStrToInt(ProductListGrid.Cells[5, r], stock) then
             begin
               SQL := 'UPDATE Products SET stocklevel=' + ProductListGrid.Cells[5,r]
                      + ' WHERE productid=' + IntToStr(id) + ';';
               isupdate := false;    //data not required by terminals
             end;
  end;
  if not MainDB.ExecRemoteSQL(SQL, true) then
         begin
           ShowMessage('Error when sending edit to server');
           ProductListGrid.Cells[c,r] := fCellValue;
         end
  else if isupdate then miApplyUpdates.Enabled := true;
end;

procedure TMainForm.ProductListGridGetEditText ( Sender: TObject; ACol,
  ARow: Integer; var Value: string ) ;
begin
  fCellValue := Value;
end;

procedure TMainForm.ProductListGridPrepareCanvas ( sender: TObject; aCol,
  aRow: Integer; aState: TGridDrawState ) ;
var
  disc: Integer;
begin
  disc := Integer(ProductListGrid.Objects[1, aRow]);
  if disc=1 then if not (gdSelected in aState) then ProductListGrid.Canvas.Font.Color := clGray;
end;

procedure TMainForm.ProductListGridSelectCell ( Sender: TObject; aCol,
  aRow: Integer; var CanSelect: Boolean ) ;
var
  disc: Integer;
begin
  if aCol>2 then
     begin
      disc := Integer(ProductListGrid.Objects[1, aRow]);
      ProductListGrid.Columns[aCol].ReadOnly := disc=1;
     end;
end;

procedure TMainForm.ProductSearchEditChange(Sender: TObject);
begin
  if TEdit(Sender).Tag=1 then
    if Length(TEdit(Sender).Text)>3 then
       begin
         ProductListQuery := 'SELECT * FROM (SELECT products.productid, products.barcode AS barcode, products.name AS name, suppliers.name AS supplier, costprice, saleprice, stocklevel, discontinued '
           + 'FROM products, suppliers where suppliers.id=products.suppliercode ) products '
           + 'AND (products.barcode LIKE "' + ProductSearchEdit.Text + '%" '
           + 'OR products.name LIKE "%' + ProductSearchEdit.Text + '%") ORDER BY barcode'
           + ' LEFT JOIN (select distinct itemid, SUM(quantity) AS sold from sales.saledetails GROUP BY Itemid) sales ON sales.itemID=products.productid '
           + ';';
         RefreshProductList;
       end
  else ProductListGrid.RowCount := 1;
end;

procedure TMainForm.ProductSearchEditEnter(Sender: TObject);
begin
  TEdit(Sender).Tag := 1;
end;

procedure TMainForm.ProductSearchEditExit(Sender: TObject);
begin
  TEdit(Sender).Tag := 0;
end;

procedure TMainForm.ResetServerDetailsButtonClick(Sender: TObject);
var
sendbuf: TTCPSendBuffer;
TimeOut: Integer;
TimeOutTime: double;
Waiting: Boolean;
begin
  ServerAddressEdit.Text := Settings.ReadString('Server', 'ServerAddress', 'localhost');
  ServerPortEdit.Text := Settings.ReadString('Server', 'ServerPort', IntToStr(DEFAULT_PORT));
end;

procedure TMainForm.ServerAddressEditChange(Sender: TObject);
begin
  ApplyServerChangesButton.Enabled := true;
  ResetServerDetailsButton.Enabled := true;
end;

procedure TMainForm.ShiftReportMenuItemClick ( Sender: TObject ) ;
var
 sendbuf: TTCPSendBuffer;
 TimeOut: Integer;
 TimeOutTime: double;
 checksum:string;
 ln: integer;
begin
  sendbuf := TTCPSendBuffer.Create(nil, nil);
  sendbuf.Header := NewHeader(ADMIN_ID, fpmRequestReport, fpdShiftReport);
  sendbuf.AddData(fShifts[ShiftGrid.Row-1].TerminalID);
  checksum := fShifts[ShiftGrid.Row-1].checksum;
  ln := Length(checksum);
  sendbuf.AddData(ln);
  sendbuf.AddData(checksum);
  MainDB.Connection.SendMessage(sendbuf);
  TimeOut := 6500;
  TimeOutTime := Now + Timeout;
  fWaiting := true;
  StatusBar.Panels[2].Text := 'Contacting server...';
  while (fWaiting) do
        begin
          MainDB.Connection.CallAction;
          Application.ProcessMessages;
         if Now>TimeOutTime then
            begin
              fWaiting := false;
              StatusBar.Panels[2].Text := 'Server failed to respond';
            end;
        end;
end;

procedure TMainForm.SupplierGridSelectCell ( Sender: TObject; aCol,
  aRow: Integer; var CanSelect: Boolean ) ;
begin
  miEmailSupplier.Enabled := Pos('@', SupplierGrid.Cells[2, aRow])>0;
end;

procedure TMainForm.TerminalListBoxSelectionChange ( Sender: TObject;
  User: boolean ) ;
var
  t: string;
  terminalnum: LongInt;
begin
  t := TerminalListBox.Items[TerminalListBox.ItemIndex];
  if TryStrToInt(Copy(t, 1, Pos(#32, t)-1), terminalnum)
   then GetShifts(terminalnum);
end;

function TMainForm.SendTerminalID(aSocket: TLSocket): integer;
var
  header: tfpHeader;
  dataStream: TMemoryStream;
  dummyint: Integer;
begin
  header.TerminalID := ADMIN_ID;
  header.MessageType := fpmTerminalLogon;
  header.datalength := SizeOf(dummyint) + SizeOf(dummyint);
  header.datatype := fpdNone;
end;

procedure TMainForm.ReceiveFile(db: TObject; tcpBuf: TTCPBuffer);
var
  ms: TMemoryStream;
  header: tfpHeader;
  tmpstrm: TMemoryStream;
  proc: TProcess;
  fn: String;
  viewerexe: String;
begin
  fWaiting := false;
  proc := nil;
  StatusBar.Panels[2].Text := '';
  tcpBuf.Data.Seek(SizeOf(header), soFromBeginning);
  tmpstrm := TMemoryStream.Create;
  tmpstrm.CopyFrom(tcpBuf.Data, tcpBuf.Header.datalength);
  fn := GetTempFileName('/tmp',RandomString(6));
  tmpstrm.SaveToFile(fn);
  tmpstrm.Free;
  viewerexe := Settings.ReadString('Options', 'PDF Viewer', '');
  if viewerexe<>''
     then proc := TProcess.Create(nil);
  if proc<> nil then
    try
      with proc do
           begin
             Executable := viewerexe;
             Parameters.Add(fn);
             Execute;
           end;
    finally
      proc.Free;
    end
  else ShowMessage('No PDF viewer set in config file');
end;

procedure TMainForm.RefreshProductList;
var
  r: integer;
  pid: Integer;
begin
  r := 1;
  ProductListGrid.RowCount := 1;
  try
//writeln(ProductListQuery);
    MainDB.SQL := ProductListQuery;
    MainDB.Open;
    WriteLn(MainDB.RecordCount);
    if MainDB.State=dsBrowse then
    while not MainDB.EOF do
      begin
        ProductListGrid.RowCount := ProductListGrid.RowCount + 1;
        pid := MainDB.FieldByName('productid').AsInteger;
        ProductListGrid.Objects[0, r] := TObject(pid);
        pid := MainDB.FieldByName('discontinued').AsInteger;
        ProductListGrid.Objects[1, r] := TObject(pid);
        ProductListGrid.Cells[0, r] := MainDB.FieldByName('barcode').AsString;
        ProductListGrid.Cells[1, r] := MainDB.FieldByName('name').AsString;
        ProductListGrid.Cells[2, r] := MainDB.FieldByName('supplier').AsString;
        ProductListGrid.Cells[3, r] := FormatFloat('0.00', MainDB.FieldByName('costprice').AsFloat);
        ProductListGrid.Cells[4, r] := FormatFloat('0.00', MainDB.FieldByName('saleprice').AsFloat);
        ProductListGrid.Cells[5, r] := MainDB.FieldByName('stocklevel').AsString;
        ProductListGrid.Cells[6, r] := MainDB.FieldByName('sold').AsString;
        Inc(r);
        MainDB.Next;
      end;
  finally
    MainDB.Close;
  end;
end;

procedure TMainForm.RefreshServicesList;
var
  r: integer;
begin
  r := 1;
  ServicesListGrid.RowCount := 1;
  try
    MainDB.SQL := 'SELECT productid, products.name AS name, saleprice, Departments.name AS dept FROM Products, Departments '
               + 'WHERE barcode IS NULL AND Departments.id=Products.dept AND productid>0 ORDER BY name;';
    MainDB.Open;
    ServicesListGrid.RowCount := MainDB.RecordCount+1;
    if MainDB.State=dsBrowse then
    while not MainDB.EOF do
      begin
//        ServicesListGrid.Objects[0,r] := TObject(MainDB.FieldByName('productid').AsInteger);
        ServicesListGrid.Cells[0, r] := MainDB.FieldByName('name').AsString;
        ServicesListGrid.Cells[1, r] := FormatFloat('0.00', MainDB.FieldByName('saleprice').AsFloat);
        ServicesListGrid.Cells[2, r] := MainDB.FieldByName('dept').AsString;
        Inc(r);
        MainDB.Next;
      end;
  finally
    MainDB.Close;
  end;
end;

procedure TMainForm.GetTerminals;
var
  r: integer;
begin
  r := 1;
  try
    fLoadingData := true;
    if Terminals.State=dsBrowse then
    while not Terminals.EOF do
      begin
        TerminalListBox.Items.Add(Terminals.FieldByName('TerminalID').AsString + #32 + Terminals.FieldByName('name').AsString);
        Inc(r);
        Terminals.Next;
      end;
  finally
    fLoadingData := false;
    if TerminalListBox.Count>0 then
       begin
         TerminalListBox.ItemIndex := 0;
       end;
  end;
end;

procedure TMainForm.GetShifts ( terminalindex: integer ) ;
var
  r: integer;
  s: String;
begin
  r := 1;
  ShiftGrid.RowCount := 1;
  try
    MainDB.SQL := 'SELECT * FROM sales.Shifts WHERE TerminalID='
                    + IntToStr(terminalindex)
                    + ' ORDER BY starttime DESC;';
    MainDB.Open;
    ShiftGrid.RowCount := MainDB.RecordCount+1;
    SetLength(fShifts, MainDB.RecordCount);
    if MainDB.State=dsBrowse then
    while not MainDB.EOF do
      try
        try
        fShifts[r-1].TerminalID := terminalindex;
        fShifts[r-1].starttime := MainDB.FieldByName('starttime').AsDateTime;
        fShifts[r-1].checksum := MainDB.FieldByName('checksum').AsString;
        s := FormatDateTime(' ddd d mmm', MainDB.FieldByName('starttime').AsDateTime);
        ShiftGrid.Cells[0, r] := s;
        ShiftGrid.ColWidths[0] := MaxIntOf( ShiftGrid.Canvas.TextWidth(s), ShiftGrid.ColWidths[0] );
        s := FormatDateTime('hh:mm', MainDB.FieldByName('starttime').AsDateTime);
        ShiftGrid.Cells[1, r] := s;
        if not MainDB.FieldByName('endtime').isNull then
            begin
              writeln(MainDb.RecNo);
            ShiftGrid.ColWidths[1] := MaxIntOf( ShiftGrid.Canvas.TextWidth(s), ShiftGrid.ColWidths[1] );
            s := FormatDateTime('hh:mm', MainDB.FieldByName('endtime').AsDateTime);
            ShiftGrid.Cells[2, r] := s;
            ShiftGrid.ColWidths[2] := MaxIntOf( ShiftGrid.Canvas.TextWidth(s), ShiftGrid.ColWidths[2] );
            s := FormatFloat('£ 0.00', MainDB.FieldByName('endcash').AsDateTime-MainDB.FieldByName('startcash').AsDateTime);
            ShiftGrid.Cells[3, r] := s;
            ShiftGrid.ColWidths[4] := MaxIntOf( ShiftGrid.Canvas.TextWidth(s), ShiftGrid.ColWidths[4] );
            s := MainDB.FieldByName('endsaleid').AsString;
            ShiftGrid.Cells[4, r] := s;
            ShiftGrid.ColWidths[4] := MaxIntOf( ShiftGrid.Canvas.TextWidth(s), ShiftGrid.ColWidths[4] );
            fShifts[r-1].endtime := MainDB.FieldByName('endtime').AsDateTime;
            end
        else fShifts[r-1].endtime := Now;
        s := MainDB.FieldByName('staffid').AsString;
        ShiftGrid.Cells[5, r] := s;
        ShiftGrid.ColWidths[5] := MaxIntOf( ShiftGrid.Canvas.TextWidth(s), ShiftGrid.ColWidths[5] );
        Except
          On E: Exception do
             writeln(E.Message);
        end;
     finally
        Inc(r);
        MainDB.Next;
      end;
  finally
    MainDB.Close;
  end;
end;

procedure TMainForm.GetSupplierList;
var
  r: integer;
  s: String;
begin
  r := 1;
  ShiftGrid.RowCount := 1;
  try
    MainDB.Close;
    MainDB.SQL := 'SELECT * FROM Suppliers ORDER BY name;';
    MainDB.Open;
    SupplierGrid.RowCount := MainDB.RecordCount+1;
    SetLength(SupplierList, MainDB.RecordCount);
    if MainDB.State=dsBrowse then
    while not MainDB.EOF do
      try
        try
        SupplierList[r-1].id := MainDB.FieldByName('id').AsInteger;
        SupplierList[r-1].name := MainDB.FieldByName('name').AsString;
        SupplierList[r-1].address := MainDB.FieldByName('address').AsString;
        SupplierList[r-1].postcode := MainDB.FieldByName('postcode').AsString;
        SupplierList[r-1].telephone := MainDB.FieldByName('telephone').AsString;
        SupplierList[r-1].email := MainDB.FieldByName('email').AsString;
        SupplierList[r-1].contact := MainDB.FieldByName('contact').AsString;
        SupplierList[r-1].website := MainDB.FieldByName('website').AsString;
        SupplierList[r-1].notes := MainDB.FieldByName('notes').AsString;
        SupplierList[r-1].accountnum := MainDB.FieldByName('accountnum').AsString;
        SupplierGrid.Cells[0, r] := MainDB.FieldByName('name').AsString;
        SupplierGrid.Cells[1, r] := MainDB.FieldByName('telephone').AsString;
        SupplierGrid.Cells[2, r] := MainDB.FieldByName('email').AsString;
        Except
          On E: Exception do
             writeln(E.Message);
        end;
     finally
        Inc(r);
        MainDB.Next;
      end;
  finally
    MainDB.Close;
  end;
end;

function TMainForm.NewSupplier(var SupplierName: string): Boolean;
begin
  Result := false;
  SupplierName := '';
  SupplierForm := TSupplierForm.Create(Self);
  SupplierForm.Caption := 'New Supplier';
  try
  if SupplierForm.ShowNew = mrOk then
    begin
      if MainDB.ExecRemoteSQL( 'INSERT INTO suppliers '
                 + ' (name, accountnum, address, postcode, telephone, email) '
                 + 'VALUES(''' + EscapeQuotes( SupplierForm.SupplierName.Text )
                 + ''',''' + SupplierForm.AccountNum.Text
                 + ''',''' + EscapeQuotes( EscapeLineEnding( SupplierForm.AddressMemo.Text ) )
                 + ''',''' + SupplierForm.PostCode.Text
                 + ''',''' + SupplierForm.Telephone.Text
                 + ''',''' + SupplierForm.Email.Text
                 + ''' );')
         then
           begin
             SupplierName := SupplierForm.SupplierName.Text;
             Result := true;
             GetSupplierList;
           end
         else
           begin
             ShowMessage('Error when adding new supplier');
           end;
    end;
  finally
  SupplierForm.Free;
  end;
end;

procedure TMainForm.GetSalesReport ( terminalID: integer; startTime, endTime: TDateTime ) ;
var
  r: integer;
  query: String;
  ReportPage: TTabSheet;
  ReportGrid: TStringGrid;
  ProductServiceFlag: String;
  saleid: Integer;
  Total: Double;
begin
  try
  query := 'SELECT sales.saledetails.saleid AS saleid, sales.salesummary.saletime AS saletime, sales.salesummary.total AS total, products.name AS itemname, sales.saledetails.quantity AS quantity, sales.saledetails.price AS price'
          + ' FROM sales.saledetails, sales.salesummary, products WHERE sales.salesummary.terminalID=' + IntToStr(terminalID)
          + ' AND sales.saledetails.saleid=sales.salesummary.saleID AND products.productID=sales.saledetails.itemID '
          + ' AND sales.salesummary.saletime>=' + DateTimeToJulianDateTimeAsString(startTime)
          {+ ' AND sales.salesummary.saletime<' + FloatToStr(endTime)
          + ' UNION SELECT sales.refunds.saleid, sales.salesummary.saletime, sales.salesummary.total, products.name, sales.refunds.quantity, sales.refunds.price '
          + ' FROM sales.refunds, sales.salesummary, products WHERE sales.salesummary.terminalID=' + IntToStr(terminalID)
          + ' AND sales.refunds.saleid=sales.salesummary.saleID AND products.productID=sales.refunds.itemID '
          + ' AND sales.salesummary.saletime>=' + FloatToStr(startTime) }
          + ' AND sales.salesummary.saletime<' + DateTimeToJulianDateTimeAsString(endTime) + ';';
            query := 'SELECT saledetails.saleid AS saleid, saletime, total, products.name AS itemname, quantity, price FROM saledetails, salesummary, products '
          + 'WHERE saledetails.saleid=salesummary.saleID AND products.productID=saledetails.itemID '
          + ' AND saletime>=' + DateTimeToJulianDateTimeAsString(startTime)
          + ' AND saletime<' + DateTimeToJulianDateTimeAsString(endTime) + ';';
writeln(query);
    MainDB.SQL := query;
    MainDB.Open;
    if MainDB.RecordCount>0 then
    begin
      saleid := 0;
      r := 1;
      ReportPage := TTabSheet.Create(Self);
      ReportPage.Caption := 'Sales Report';
      ReportPage.Color := clWhite;
      ReportPage.PageControl := ReportPageControl;
      ReportPageControl.ActivePage := ReportPage;
      BasePageControl.PageIndex := 0;
      ReportGrid := TStringGrid.Create(Self);
      ReportGrid.Parent := ReportPage;
      ReportGrid.Align := alClient;
      ReportGrid.AutoFillColumns := true;
      ReportGrid.RowCount := MainDB.RecordCount*2;
      ReportGrid.ColCount := 4;
      ReportGrid.FixedCols := 0;
      ReportGrid.ColWidths[0] := 120;
      While not MainDB.EOF do
           begin
             if MainDB.FieldByName('saleid').AsInteger > saleid then
                begin
                  if r>1 then
                     begin
                       ReportGrid.Cells[2, r] := 'TOTAL';
                       ReportGrid.Cells[3, r] := FormatFloat(CurrencyString + ' 0.00', total);
                       Inc(r,2);
                     end;
                  saleid := MainDB.FieldByName('saleid').AsInteger;
                  ReportGrid.Cells[0, r] := MainDB.FieldByName('saleid').AsString;
                  ReportGrid.Cells[1, r] := FormatDateTime('d mmm yyyy   hh:mm:ss', MainDB.FieldByName('saletime').AsFloat);
                  Total := MainDB.FieldByName('total').AsFloat;
                  ReportGrid.RowCount := ReportGrid.RowCount+2;
                  Inc(r);
                end;
             ReportGrid.Cells[1, r] := MainDB.FieldByName('itemname').AsString;
             ReportGrid.Cells[2, r] := 'x' + MainDB.FieldByName('quantity').AsString;
             ReportGrid.Cells[3, r] := FormatFloat(CurrencyString + ' 0.00', MainDB.FieldByName('price').AsFloat);
             Inc(r);
             MainDB.Next;
           end;
      ReportGrid.Cells[2, r] := 'TOTAL';
      ReportGrid.Cells[3, r] := FormatFloat(CurrencyString + ' 0.00', total);
      ReportGrid.RowCount := r+2;
      ReportGrid.Visible := true;
//      ReportGrid.ColWidths[1] := ReportGrid.Width - ReportGrid.ColWidths[0] - ReportGrid.ColWidths[2] - ReportGrid.ColWidths[3]-20;
    end;
  finally
    MainDB.Close;
  end;
end;

procedure TMainForm.GetEvents ( terminalID: integer; startTime,
  endTime: TDateTime ) ;
var
  terminalstr: String;
  r: Integer;
  s: String;
begin
  if terminalID<0 then terminalstr := '' else terminalstr := ' events.TerminalLog.terminalID=' + IntToStr(terminalID) + ' AND ';
  r := 1;
  EventsGrid.RowCount := 1;
  try
    MainDB.SQL := 'SELECT * FROM events.TerminalLog WHERE '
                    + terminalstr
                    + 'events.TerminalLog.eventtime>=' + DateTimeToJulianDateTimeAsString((startTime))
                    + ' AND events.TerminalLog.eventtime<=' + DateTimeToJulianDateTimeAsString((endTime))
                    + ' ORDER BY eventtime DESC;';
    MainDB.Open;    Now;
    EventsGrid.RowCount := MainDB.RecordCount+1;
    if MainDB.State=dsBrowse then
    while not MainDB.EOF do
      try
        try
        s := FormatDateTime(' ddd d mmm', MainDB.FieldByName('eventtime').AsDateTime);
        EventsGrid.Cells[0, r] := s;
        EventsGrid.ColWidths[0] := MaxIntOf( EventsGrid.Canvas.TextWidth(s), EventsGrid.ColWidths[0] );
        s := FormatDateTime('hh:mm:ss', MainDB.FieldByName('eventtime').AsDateTime);
        EventsGrid.Cells[1, r] := s;
        EventsGrid.ColWidths[1] := MaxIntOf( EventsGrid.Canvas.TextWidth(s), EventsGrid.ColWidths[1] );
        s := MainDB.FieldByName('TerminalID').AsString;
        EventsGrid.Cells[2, r] := s;
        EventsGrid.ColWidths[2] := MaxIntOf( EventsGrid.Canvas.TextWidth(s), EventsGrid.ColWidths[2] );
        s := MainDB.FieldByName('StaffID').AsString;
        EventsGrid.Cells[3, r] := s;
        EventsGrid.ColWidths[4] := MaxIntOf( EventsGrid.Canvas.TextWidth(s), EventsGrid.ColWidths[4] );
        s := MainDB.FieldByName('event').AsString;
        EventsGrid.Cells[4, r] := s;
        EventsGrid.ColWidths[4] := MaxIntOf( EventsGrid.Canvas.TextWidth(s), EventsGrid.ColWidths[4] );
        Except
          On E: Exception do
             writeln(E.Message);
        end;
     finally
        Inc(r);
        MainDB.Next;
      end;
  finally
    MainDB.Close;
  end;
end;


end.

